package org.dromara.oce.controller.inner;

import cn.dev33.satoken.annotation.SaIgnore;
import jakarta.servlet.http.HttpServletResponse;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.RequiredArgsConstructor;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.dromara.common.excel.utils.ExcelUtil;
import org.dromara.oce.domain.req.OceAdvertiserDataAnalysisQo;
import org.dromara.oce.domain.vo.OceAdvertiserDataAnalysisExcel;
import org.dromara.oce.domain.vo.OceAdvertiserDataAnalysisVo;
import org.dromara.oce.service.OceAdvertiserDataAnalysisService;
import org.jetbrains.annotations.NotNull;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.io.IOException;
import java.io.OutputStream;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 巨量数据分析
 * @desc:
 * @author: hhl
 * @date：2025/6/24 18:24
 * @module 对内接口
 */
@SaIgnore
@RequiredArgsConstructor
@RestController
@RequestMapping("/inner/oceAdvertiserDataAnalysis")
public class OceAdvertiserDataAnalysisController {

    private final OceAdvertiserDataAnalysisService oceAdvertiserDataAnalysisService;

    /**
     * 导出广告主数据分析列表
     */
    @PostMapping("/export")
    public void export(OceAdvertiserDataAnalysisQo bo, HttpServletResponse response) throws IOException {
        List<OceAdvertiserDataAnalysisVo> list = oceAdvertiserDataAnalysisService.queryList(bo);
        Map<String, List<OceAdvertiserDataAnalysisVo>> collect = list.stream().collect(Collectors.groupingBy(OceAdvertiserDataAnalysisVo::getThirdAdvertiserId));

        List<OceAdvertiserDataAnalysisExcel> resp = new ArrayList<>();
        for (Map.Entry<String, List<OceAdvertiserDataAnalysisVo>> entry : collect.entrySet()) {
            OceAdvertiserDataAnalysisExcel re = new OceAdvertiserDataAnalysisExcel();
            re.setThirdAdvertiserId(entry.getKey());
            re.setSexList(entry.getValue().stream().filter(item -> item.getDimensionsType().equals("gender")).collect(Collectors.toList()));
            re.setAgeList(entry.getValue().stream().filter(item -> item.getDimensionsType().equals("age")).collect(Collectors.toList()));
            re.setAgeV2List(entry.getValue().stream().filter(item -> item.getDimensionsType().equals("age_v2")).collect(Collectors.toList()));
            re.setPlatList(entry.getValue().stream().filter(item -> item.getDimensionsType().equals("platform")).collect(Collectors.toList()));
            re.setAcList(entry.getValue().stream().filter(item -> item.getDimensionsType().equals("ac")).collect(Collectors.toList()));
            re.setProvinceList(entry.getValue().stream().filter(item -> item.getDimensionsType().equals("province_name")).collect(Collectors.toList()));
            re.setCityList(entry.getValue().stream().filter(item -> item.getDimensionsType().equals("city_name")).collect(Collectors.toList()));
            re.setAppList(entry.getValue().stream().filter(item -> item.getDimensionsType().equals("app_code")).collect(Collectors.toList()));
            resp.add(re);
        }

        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("数据分析"+bo.getStartDate());

        int rowNum = 0;
        int rowNum2 = 0;
        int rowNum3 = 0;
        int rowNum4 = 0;
        int rowNum5 = 0;
        int rowNum6 = 0;
        int rowNum7 = 0;
        int rowNum8 = 0;
        Map<Integer, Row> map = new HashMap<>();
        for (OceAdvertiserDataAnalysisExcel excel : resp) {
            int cellNum = 0;
            if (excel.getSexList().size() == 0 && excel.getSexList().size() == 0) {
                continue;
            }

            Row headerRow = sheet.createRow(rowNum);
            headerRow.createCell(cellNum).setCellValue("广告主");
            headerRow.createCell(cellNum + 1).setCellValue(excel.getThirdAdvertiserId());
            //标题
            createCells(sheet, rowNum + 1, cellNum);

            for (OceAdvertiserDataAnalysisVo vo : excel.getCityList()) {
                Row row = sheet.createRow(rowNum + 2);
                setCellsValue(cellNum, vo, row);
                map.put(rowNum, row);
                rowNum++;
            }
            cellNum = cellNum + 14;


            for (OceAdvertiserDataAnalysisVo vo : excel.getProvinceList()) {
                Row row = map.get(rowNum2);
                if (row == null) {
                    row = sheet.createRow(rowNum2 + 2);
                }
                setCellsValue(cellNum, vo, row);
                map.put(rowNum2, row);
                rowNum2++;
            }
            cellNum = cellNum + 14;

            for (OceAdvertiserDataAnalysisVo vo : excel.getSexList()) {
                Row row = map.get(rowNum3);
                if (row == null) {
                    row = sheet.createRow(rowNum3 + 2);
                }
                setCellsValue(cellNum, vo, row);
                map.put(rowNum3, row);
                rowNum3++;
            }
            cellNum = cellNum + 14;


            for (OceAdvertiserDataAnalysisVo vo : excel.getAgeList()) {
                Row row = map.get(rowNum4);
                if (row == null) {
                    row = sheet.createRow(rowNum4 + 2);
                }
                setCellsValue(cellNum, vo, row);
                map.put(rowNum4, row);
                rowNum4++;
            }
            cellNum = cellNum + 14;

            for (OceAdvertiserDataAnalysisVo vo : excel.getAgeV2List()) {
                Row row = map.get(rowNum5);
                if (row == null) {
                    row = sheet.createRow(rowNum5 + 2);
                }
                setCellsValue(cellNum, vo, row);
                map.put(rowNum5, row);
                rowNum5++;
            }
            cellNum = cellNum + 14;

            for (OceAdvertiserDataAnalysisVo vo : excel.getPlatList()) {
                Row row = map.get(rowNum6);
                if (row == null) {
                    row = sheet.createRow(rowNum6 + 2);
                }
                setCellsValue(cellNum, vo, row);
                map.put(rowNum6, row);
                rowNum6++;
            }
            cellNum = cellNum + 14;

            for (OceAdvertiserDataAnalysisVo vo : excel.getAcList()) {
                Row row = map.get(rowNum7);
                if (row == null) {
                    row = sheet.createRow(rowNum7 + 2);
                }
                setCellsValue(cellNum, vo, row);
                map.put(rowNum7, row);
                rowNum7++;
            }
            cellNum = cellNum + 14;

            for (OceAdvertiserDataAnalysisVo vo : excel.getAppList()) {
                Row row = map.get(rowNum8);
                if (row == null) {
                    row = sheet.createRow(rowNum8 + 2);
                }
                setCellsValue(cellNum, vo, row);
                map.put(rowNum8, row);
                rowNum8++;
            }
            cellNum = cellNum + 14;


            List<Integer> rowList = Arrays.asList(rowNum, rowNum2, rowNum3, rowNum4, rowNum5, rowNum6, rowNum7, rowNum8);
            Integer maxNum = rowList.stream().max(Integer::compare).orElse(0) + 1;
            Row lastRow = sheet.createRow(maxNum);
            lastRow.createCell(0).setCellValue("====================================================================");
            rowNum = rowNum2 = rowNum3 = rowNum4 = rowNum5 = rowNum6 = rowNum7 = rowNum8 = maxNum+1;
        }


//        try (FileOutputStream outputStream = new FileOutputStream("C:\\Users\\hhl\\Desktop\\test\\aaa.xlsx")) {
//            workbook.write(outputStream);
//        }

        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setHeader("Content-Disposition", "attachment; filename=分析数据.xlsx");
        // 写入响应流
        try (OutputStream out = response.getOutputStream()) {
            workbook.write(out);
        } finally {
            workbook.close();
        }
    }

    private static void setCellsValue(int cellNum, OceAdvertiserDataAnalysisVo vo, Row row) {
        row.createCell(cellNum).setCellValue(vo.getDimensionsName());
        row.createCell(cellNum + 1).setCellValue(vo.getStatCost() + "");
        row.createCell(cellNum + 2).setCellValue(vo.getShowCnt());
        row.createCell(cellNum + 3).setCellValue(vo.getClickCnt());
        row.createCell(cellNum + 4).setCellValue(vo.getCpmPlatform() + "");
        row.createCell(cellNum + 5).setCellValue(vo.getCpcPlatform() + "");
        row.createCell(cellNum + 6).setCellValue(vo.getCtr());
        row.createCell(cellNum + 7).setCellValue(vo.getConvertCnt() + "");
        row.createCell(cellNum + 8).setCellValue(vo.getConversionRate() + "");
        row.createCell(cellNum + 9).setCellValue(vo.getConversionCost() + "");
        row.createCell(cellNum + 10).setCellValue(0);
        row.createCell(cellNum + 11).setCellValue(0);
        row.createCell(cellNum + 12).setCellValue(0);
    }

    @NotNull
    private static Row createCells(Sheet sheet, int rowNum, int cellNum) {
        Row headerRow = sheet.createRow(rowNum);

        headerRow.createCell(cellNum).setCellValue("城市");
        headerRow.createCell(cellNum + 1).setCellValue("消耗");
        headerRow.createCell(cellNum + 2).setCellValue("展示数");
        headerRow.createCell(cellNum + 3).setCellValue("点击数");
        headerRow.createCell(cellNum + 4).setCellValue("平均千次展现费用(元)");
        headerRow.createCell(cellNum + 5).setCellValue("平均点击单价(元)");
        headerRow.createCell(cellNum + 6).setCellValue("点击率%");
        headerRow.createCell(cellNum + 7).setCellValue("转化数");
        headerRow.createCell(cellNum + 8).setCellValue("转化率%");
        headerRow.createCell(cellNum + 9).setCellValue("平均转化成本");
        headerRow.createCell(cellNum + 10).setCellValue("深度转化数");
        headerRow.createCell(cellNum + 11).setCellValue("深度转化成本");
        headerRow.createCell(cellNum + 12).setCellValue("深度转化率%");
        cellNum += 14;

        headerRow.createCell(cellNum).setCellValue("省份");
        headerRow.createCell(cellNum + 1).setCellValue("消耗");
        headerRow.createCell(cellNum + 2).setCellValue("展示数");
        headerRow.createCell(cellNum + 3).setCellValue("点击数");
        headerRow.createCell(cellNum + 4).setCellValue("平均千次展现费用(元)");
        headerRow.createCell(cellNum + 5).setCellValue("平均点击单价(元)");
        headerRow.createCell(cellNum + 6).setCellValue("点击率%");
        headerRow.createCell(cellNum + 7).setCellValue("转化数");
        headerRow.createCell(cellNum + 8).setCellValue("转化率%");
        headerRow.createCell(cellNum + 9).setCellValue("平均转化成本");
        headerRow.createCell(cellNum + 10).setCellValue("深度转化数");
        headerRow.createCell(cellNum + 11).setCellValue("深度转化成本");
        headerRow.createCell(cellNum + 12).setCellValue("深度转化率%");
        cellNum += 14;

        headerRow.createCell(cellNum).setCellValue("性别");
        headerRow.createCell(cellNum + 1).setCellValue("消耗");
        headerRow.createCell(cellNum + 2).setCellValue("展示数");
        headerRow.createCell(cellNum + 3).setCellValue("点击数");
        headerRow.createCell(cellNum + 4).setCellValue("平均千次展现费用(元)");
        headerRow.createCell(cellNum + 5).setCellValue("平均点击单价(元)");
        headerRow.createCell(cellNum + 6).setCellValue("点击率%");
        headerRow.createCell(cellNum + 7).setCellValue("转化数");
        headerRow.createCell(cellNum + 8).setCellValue("转化率%");
        headerRow.createCell(cellNum + 9).setCellValue("平均转化成本");
        headerRow.createCell(cellNum + 10).setCellValue("深度转化数");
        headerRow.createCell(cellNum + 11).setCellValue("深度转化成本");
        headerRow.createCell(cellNum + 12).setCellValue("深度转化率%");

        cellNum += 14;
        headerRow.createCell(cellNum).setCellValue("年龄");
        headerRow.createCell(cellNum + 1).setCellValue("消耗");
        headerRow.createCell(cellNum + 2).setCellValue("展示数");
        headerRow.createCell(cellNum + 3).setCellValue("点击数");
        headerRow.createCell(cellNum + 4).setCellValue("平均千次展现费用(元)");
        headerRow.createCell(cellNum + 5).setCellValue("平均点击单价(元)");
        headerRow.createCell(cellNum + 6).setCellValue("点击率%");
        headerRow.createCell(cellNum + 7).setCellValue("转化数");
        headerRow.createCell(cellNum + 8).setCellValue("转化率%");
        headerRow.createCell(cellNum + 9).setCellValue("平均转化成本");
        headerRow.createCell(cellNum + 10).setCellValue("深度转化数");
        headerRow.createCell(cellNum + 11).setCellValue("深度转化成本");
        headerRow.createCell(cellNum + 12).setCellValue("深度转化率%");

        cellNum += 14;
        headerRow.createCell(cellNum).setCellValue("年龄细分");
        headerRow.createCell(cellNum + 1).setCellValue("消耗");
        headerRow.createCell(cellNum + 2).setCellValue("展示数");
        headerRow.createCell(cellNum + 3).setCellValue("点击数");
        headerRow.createCell(cellNum + 4).setCellValue("平均千次展现费用(元)");
        headerRow.createCell(cellNum + 5).setCellValue("平均点击单价(元)");
        headerRow.createCell(cellNum + 6).setCellValue("点击率%");
        headerRow.createCell(cellNum + 7).setCellValue("转化数");
        headerRow.createCell(cellNum + 8).setCellValue("转化率%");
        headerRow.createCell(cellNum + 9).setCellValue("平均转化成本");
        headerRow.createCell(cellNum + 10).setCellValue("深度转化数");
        headerRow.createCell(cellNum + 11).setCellValue("深度转化成本");
        headerRow.createCell(cellNum + 12).setCellValue("深度转化率%");

        cellNum += 14;
        headerRow.createCell(cellNum).setCellValue("平台");
        headerRow.createCell(cellNum + 1).setCellValue("消耗");
        headerRow.createCell(cellNum + 2).setCellValue("展示数");
        headerRow.createCell(cellNum + 3).setCellValue("点击数");
        headerRow.createCell(cellNum + 4).setCellValue("平均千次展现费用(元)");
        headerRow.createCell(cellNum + 5).setCellValue("平均点击单价(元)");
        headerRow.createCell(cellNum + 6).setCellValue("点击率%");
        headerRow.createCell(cellNum + 7).setCellValue("转化数");
        headerRow.createCell(cellNum + 8).setCellValue("转化率%");
        headerRow.createCell(cellNum + 9).setCellValue("平均转化成本");
        headerRow.createCell(cellNum + 10).setCellValue("深度转化数");
        headerRow.createCell(cellNum + 11).setCellValue("深度转化成本");
        headerRow.createCell(cellNum + 12).setCellValue("深度转化率%");

        cellNum += 14;
        headerRow.createCell(cellNum).setCellValue("网络");
        headerRow.createCell(cellNum + 1).setCellValue("消耗");
        headerRow.createCell(cellNum + 2).setCellValue("展示数");
        headerRow.createCell(cellNum + 3).setCellValue("点击数");
        headerRow.createCell(cellNum + 4).setCellValue("平均千次展现费用(元)");
        headerRow.createCell(cellNum + 5).setCellValue("平均点击单价(元)");
        headerRow.createCell(cellNum + 6).setCellValue("点击率%");
        headerRow.createCell(cellNum + 7).setCellValue("转化数");
        headerRow.createCell(cellNum + 8).setCellValue("转化率%");
        headerRow.createCell(cellNum + 9).setCellValue("平均转化成本");
        headerRow.createCell(cellNum + 10).setCellValue("深度转化数");
        headerRow.createCell(cellNum + 11).setCellValue("深度转化成本");
        headerRow.createCell(cellNum + 12).setCellValue("深度转化率%");

        cellNum += 14;
        headerRow.createCell(cellNum).setCellValue("首选位置");
        headerRow.createCell(cellNum + 1).setCellValue("消耗");
        headerRow.createCell(cellNum + 2).setCellValue("展示数");
        headerRow.createCell(cellNum + 3).setCellValue("点击数");
        headerRow.createCell(cellNum + 4).setCellValue("平均千次展现费用(元)");
        headerRow.createCell(cellNum + 5).setCellValue("平均点击单价(元)");
        headerRow.createCell(cellNum + 6).setCellValue("点击率%");
        headerRow.createCell(cellNum + 7).setCellValue("转化数");
        headerRow.createCell(cellNum + 8).setCellValue("转化率%");
        headerRow.createCell(cellNum + 9).setCellValue("平均转化成本");
        headerRow.createCell(cellNum + 10).setCellValue("深度转化数");
        headerRow.createCell(cellNum + 11).setCellValue("深度转化成本");
        headerRow.createCell(cellNum + 12).setCellValue("深度转化率%");


        return headerRow;
    }

    /**
     * 多列表多数据
     */
    @GetMapping("/exportTemplateMuliti")
    public void exportTemplateMuliti(HttpServletResponse response) {
        Map<String, String> map = new HashMap<>();
        map.put("title1", "标题1");
        map.put("title2", "标题2");
        map.put("title3", "标题3");
        map.put("title4", "标题4");
        map.put("author", "Lion Li");
        List<TestObj1> list1 = new ArrayList<>();
        list1.add(new TestObj1("list1测试1", "list1测试2", "list1测试3"));
        list1.add(new TestObj1("list1测试4", "list1测试5", "list1测试6"));
        list1.add(new TestObj1("list1测试7", "list1测试8", "list1测试9"));
        List<TestObj1> list2 = new ArrayList<>();
        list2.add(new TestObj1("list2测试1", "list2测试2", "list2测试3"));
        list2.add(new TestObj1("list2测试4", "list2测试5", "list2测试6"));
        List<TestObj1> list3 = new ArrayList<>();
        list3.add(new TestObj1("list3测试1", "list3测试2", "list3测试3"));
        List<TestObj1> list4 = new ArrayList<>();
        list4.add(new TestObj1("list4测试1", "list4测试2", "list4测试3"));
        list4.add(new TestObj1("list4测试4", "list4测试5", "list4测试6"));
        list4.add(new TestObj1("list4测试7", "list4测试8", "list4测试9"));
        list4.add(new TestObj1("list4测试10", "list4测试11", "list4测试12"));
        Map<String, Object> multiListMap = new HashMap<>();
        multiListMap.put("map", map);
        multiListMap.put("data1", list1);
        multiListMap.put("data2", list2);
        multiListMap.put("data3", list3);
        multiListMap.put("data4", list4);
        ExcelUtil.exportTemplateMultiList(multiListMap, "多列表.xlsx", "excel/多列表.xlsx", response);
    }


    @Data
    @AllArgsConstructor
    static class TestObj1 {
        private String test1;
        private String test2;
        private String test3;
    }

    @Data
    @AllArgsConstructor
    static class TestObj {
        private String name;
        private String list1;
        private String list2;
        private String list3;
        private String list4;
    }


}
